#! /bin/bash
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

${HIVE_HOME} -S -e "

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

insert into zx_dwb.dwb_sign_detail partition(dt)
select
       ctud.id as course_id,
       base_id,
       ctud.class_id as course_class_id,
       class_date,
       content,
       teacher_id,
       teacher_name,
       job_number,
       classroom_id,
       classroom_name,
       is_outline,
       class_mode,
       is_stage_exam,
       is_pay,
       tutor_teacher_id,
       tutor_teacher_name,
       tutor_job_number,
       is_subsidy,
       answer_teacher_id,
       answer_teacher_name,
       answer_job_number,
       ctud.remark as course_remark,
       ctud.create_time as course_create_time,

       tssr.id as signin_id,
       normal_class_flag,
       time_table_id,
       tssr.class_id as signin_class_id,
       student_id,
       signin_time,
       signin_date,
       inner_flag,
       signin_type,
       share_state,
       inner_ip,

       tctt.id as time_id,
       tctt.class_id as time_class_id,
       morning_template_id,
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date,
       tctt.create_time as time_create_time,
       create_person,
       tctt.remark as time_remark,
       substring(ctud.create_time,1,10) as dt
--课表
from zx_dwd.dim_course_table_upload_detail ctud
--打卡表
left join zx_dwd.fact_tbh_student_signin_record tssr
on tssr.class_id = ctud.class_id and tssr.signin_date = ctud.class_date
--作息表
left join zx_dwd.dim_tbh_class_time_table tctt
on tssr.time_table_id = tctt.id
where tctt.use_begin_date <= ctud.class_date
      and tctt.use_end_date >= ctud.class_date
and dt = ${TD_DATE};

insert into zx_dwb.dwb_leave_detail partition(dt)
select
       ctud.id as course_id,
       base_id,
       ctud.class_id as course_class_id,
       class_date,
       content ,
       teacher_id,
       teacher_name,
       job_number,
       classroom_id,
       classroom_name,
       is_outline,
       class_mode,
       is_stage_exam,
       is_pay,
       tutor_teacher_id,
       tutor_teacher_name,
       tutor_job_number,
       is_subsidy,
       answer_teacher_id,
       answer_teacher_name,
       answer_job_number,
       ctud.remark as course_remark,
       ctud.create_time as course_create_time,

       sla.id as leave_id,
       sla.class_id leave_class_id,
       student_id,
       audit_state,
       audit_person,
       audit_time,
       audit_remark,
       leave_type,
       leave_reason,
       begin_time,
       begin_time_type,
       end_time,
       end_time_type,
       days,
       cancel_state,
       cancel_time,
       old_leave_id,
       leave_remark,
       valid_state,
       sla.create_time as leave_create_time,

       tctt.id as time_id,
       tctt.class_id as time_class_id,
       morning_template_id,
       morning_begin_time,
       morning_end_time,
       afternoon_template_id,
       afternoon_begin_time,
       afternoon_end_time,
       evening_template_id,
       evening_begin_time,
       evening_end_time,
       use_begin_date,
       use_end_date,
       tctt.create_time as time_create_time,
       create_person,
       tctt.remark,
       substring(ctud.create_time,1,10) as dt
from
    zx_dwd.dim_course_table_upload_detail ctud,
    zx_dwd.fact_student_leave_apply sla,
    zx_dwd.dim_tbh_class_time_table tctt
where ctud.class_id=sla.class_id
    and ctud.class_id=tctt.class_id
    --and ctud.content is NOT NULL and ctud.content != '开班典礼'
    --作息时间匹配
    and tctt.use_begin_date <= ctud.class_date
    and tctt.use_end_date >= ctud.class_date
    -- 请假开始时间  <= 上课开始时间  <= 请假结束时间
--     and sla.begin_time <= concat(ctud.class_date, ' ', tctt.morning_begin_time)
--     and concat(ctud.class_date, ' ', tctt.morning_begin_time) <= sla.end_time
    --请假是否有效
    and sla.audit_state=1
    and sla.cancel_state=0
    and sla.valid_state=1
and dt = ${TD_DATE};